package by.bsu.touragency.db.dao;

import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.apache.log4j.Logger;

import by.bsu.touragency.db.beans.Star;
import by.bsu.touragency.db.connection.DBConnector;

public class StarDAO extends AbstractDAO {

	public static final String SQL_STAR_CREATE = " INSERT INTO tourAgencyDB.star(name, about) VALUES (? , ?);";
	public static final String SQL_STAR_CREATE_GET_ID = "SELECT * FROM tourAgencyDB.star WHERE name = ? ;";
	public static final String SQL_STAR_READ_BY_ID = "SELECT * FROM tourAgencyDB.star WHERE (id= ?);";
	public static final String SQL_STAR_DELETE = "DELETE FROM tourAgencyDB.star WHERE id = ?";
	public static final String SQL_STAR_IS_EXIST = " SELECT COUNT(*) AS rowcount FROM tourAgencyDB.star WHERE name = ? AND about = ? ;";
	public static final String SQL_STAR_IS_IN_USE = " SELECT COUNT(*) AS rowcount FROM tourAgencyDB.hotel WHERE starId = ? ;";

	private static Logger logger = Logger.getLogger("log");

	public StarDAO() {
		super();
	}

	public boolean isExist(Star star) {
		logger.info("Is this star type by exist");
		try {// 1
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_STAR_IS_EXIST);
			int n;
			try {// 3
				statement.setString(1, star.getName());
				statement.setString(2, star.getAbout());
				resultSet2 = statement.executeQuery();
				n = -2;
				resultSet2.next();
				n = resultSet2.getInt("rowcount");
			} finally {
			}// 3
			if (resultSet2 != null) {
				resultSet2.close();
			}
			if (statement != null) {
				statement.close();
			}
			if (n == 0 || n == -2) {
				logger.debug("Star type doesn't exist");
				return false;
			} else {
				logger.debug("This star type already exist");
				return true;
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
			return false;
		} catch (Exception e) {
			logger.error("Undefined exeption");
			return false;
		}

	}

	public boolean isInUse(Star star) {

		logger.info("Is this star type in use");
		try {// 1
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_STAR_IS_IN_USE);
			int n;
			try {// 3
				statement.setInt(1, star.getId());
				resultSet2 = statement.executeQuery();
				n = -2;
				resultSet2.next();
				n = resultSet2.getInt("rowcount");
			} finally {
			}// 3
			if (resultSet2 != null) {
				resultSet2.close();
			}
			if (statement != null) {
				statement.close();
			}
			if (n == 0 || n == -2) {
				logger.debug("Star type is NOT in use");
				return false;
			} else {
				logger.debug("This star type is in use");
				return true;
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
			return false;
		} catch (Exception e) {
			logger.error("Undefined exeption");
			return false;
		}

	}

	public Star create(Star star) {
		logger.info("Create star type");
		try {
			if (isExist(star)) {
				connector = new DBConnector();
				PreparedStatement statement = connector
						.getPreparedStatement(SQL_STAR_CREATE);
				statement.setString(1, star.getName());
				statement.setString(2, star.getAbout());
				statement.executeUpdate();

			}
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_STAR_CREATE_GET_ID);
			statement.setString(1, star.getName());
			resultSet = statement.executeQuery();

			if (resultSet.next()) {
				star.setId(resultSet.getInt("id"));
				star.setAbout(resultSet.getString("about"));
			}

			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}

		return star;
	}

	// R
	public Star readById(int id) {
		logger.info("Read star type by id");
		Star star = new Star();
		try {
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_STAR_READ_BY_ID);
			statement.setInt(1, id);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				star.setId(resultSet.getInt("id"));
				star.setName(resultSet.getString("name"));
				star.setAbout(resultSet.getString("about"));
			}
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}
		return star;
	}

	public void delete(Star star) {
		if (!isInUse(star)) {
			logger.info("Delete star type");
			try {
				connector = new DBConnector();
				PreparedStatement statement = connector
						.getPreparedStatement(SQL_STAR_DELETE);
				statement.setInt(1, star.getId());
				statement.executeUpdate();
				if (statement != null) {
					statement.close();
				}
			} catch (SQLException e) {
				logger.error("SQL exeption");
			} catch (Exception e) {
				logger.error("Undefined exeption");
			} finally {
				logger.debug("Close connection");
				close();
			}
		}
	}

}
